#!/usr/bin/env python3
"""
数据库管理工具
提供数据库初始化、重置、备份等功能
"""

import sys
from database import init_db, SessionLocal, DBUser, DBBookBorrow, engine, Base
from sqlalchemy import text

def check_database_status():
    """检查数据库状态"""
    print("=" * 50)
    print("数据库状态检查")
    print("=" * 50)
    
    try:
        db = SessionLocal()
        
        # 检查用户表
        user_count = db.query(DBUser).count()
        print(f"✓ 用户表: {user_count} 条记录")
        
        # 检查图书借阅数据表
        borrow_count = db.query(DBBookBorrow).count()
        print(f"✓ 图书借阅数据表: {borrow_count} 条记录")
        
        # 显示用户信息
        if user_count > 0:
            print("\n用户列表:")
            users = db.query(DBUser).all()
            for user in users:
                print(f"  - {user.username} ({user.name}) - {user.role.value}")
        
        # 显示最近的图书借阅数据
        if borrow_count > 0:
            print("\n最近5条图书借阅数据:")
            recent_borrow = db.query(DBBookBorrow).order_by(DBBookBorrow.borrow_date.desc()).limit(5).all()
            for borrow in recent_borrow:
                print(f"  - {borrow.borrow_date} {borrow.borrower}: {borrow.borrowed_book} ({borrow.borrow_status.value})")
        
        db.close()
        
    except Exception as e:
        print(f"✗ 检查数据库状态时出错: {e}")

def reset_database():
    """重置数据库（删除所有表并重新创建）"""
    print("=" * 50)
    print("重置数据库")
    print("=" * 50)
    
    try:
        # 删除所有表
        print("正在删除所有表...")
        Base.metadata.drop_all(bind=engine)
        print("✓ 所有表已删除")
        
        # 重新初始化
        print("开始重新初始化数据库...")
        init_db()
        
    except Exception as e:
        print(f"✗ 重置数据库时出错: {e}")

def clear_borrow_data():
    """清空图书借阅数据"""
    print("=" * 50)
    print("清空图书借阅数据")
    print("=" * 50)
    
    try:
        db = SessionLocal()
        count = db.query(DBBookBorrow).count()
        
        if count == 0:
            print("图书借阅数据表已经是空的")
            return
        
        db.query(DBBookBorrow).delete()
        db.commit()
        print(f"✓ 已清空 {count} 条图书借阅数据")
        db.close()
        
    except Exception as e:
        print(f"✗ 清空图书借阅数据时出错: {e}")

def show_tables():
    """显示所有表"""
    print("=" * 50)
    print("数据库表结构")
    print("=" * 50)
    
    try:
        with engine.connect() as conn:
            # 获取所有表名（MySQL语法）
            result = conn.execute(text("SHOW TABLES"))
            tables = result.fetchall()
            
            print("数据库中的表:")
            for table in tables:
                table_name = table[0]
                print(f"\n表名: {table_name}")
                
                # 获取表结构（MySQL语法）
                result = conn.execute(text(f"DESCRIBE {table_name}"))
                columns = result.fetchall()
                
                print("  字段信息:")
                for col in columns:
                    print(f"    {col[0]} ({col[1]}) - {'主键' if col[3] == 'PRI' else ''}{'非空' if col[2] == 'NO' else '可空'}")
                
    except Exception as e:
        print(f"✗ 显示表结构时出错: {e}")
